package org.fao.pims.persistence;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.ListIterator;
import org.fao.pims.model.*;;



public class InfoItemDaoImpl extends BaseDao implements InfoItemDao {

	private Connection conn;
	
	public int insertInfoItem(SPModel model) throws DataAccessException {
		
		int itemId = 0;
		
		
		PreparedStatement pstmt = null;
    	
    	try {
      		
    		conn = this.getDatasource().getConnection();  
         
      		String statement = "INSERT INTO info_item SET " +      									
							   "TAXON = ?, " +
							   "CONTENT = ?, " +
							   "FORMAT = ?, " +
							   "CATEGORY = ?";
      		
      		pstmt = conn.prepareStatement( statement );
      		
      			
  			pstmt.setInt(1, model.getInfoItem().getTaxonId());
  			
  			
  			if(!"".equals(model.getInfoItem().getAssociations())) {
				
				
				pstmt.setString(2, model.getInfoItem().getAssociations());
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "ASSOCIATION");
				
				pstmt.execute();
				
				
			}
  			
  			if(!"".equals( model.getInfoItem().getBehaviour() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getBehaviour() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "BEHAVIOUR");
				
				pstmt.execute();
				
				
			}
  			
  			if(!"".equals( model.getInfoItem().getCitology() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getCitology() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "CITOLOGY");
				
				pstmt.execute();
				
				
			}
  			
  			if(!"".equals( model.getInfoItem().getConservationStatus() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getConservationStatus() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "CONSERVATION_STATUS");
				
				pstmt.execute();
				
				
			}
  			
  			if(!"".equals( model.getInfoItem().getCyclicity() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getCyclicity() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "CYCLICITY");
				
				pstmt.execute();
				
				
			}
  			
  			if(!"".equals( model.getInfoItem().getDiagnosticDescription() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getDiagnosticDescription() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "DIAGNOSTIC_DESCRIPTION");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals(model.getInfoItem().getDiseases())) {
				
				pstmt.setString(2, model.getInfoItem().getDiseases());
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "DISEASES");
				
				pstmt.execute();
				
			}
			
			if(!"".equals( model.getInfoItem().getDispersal() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getDispersal() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "DISPERSAL");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getDistribution() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getDistribution() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "DISTRIBUTION");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getEvolution() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getEvolution() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "EVOLUTION");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getGeneralDescription() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getGeneralDescription() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "GENERAL_DESCRIPTION");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getGenetics() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getGenetics() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "GENETICS");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getGrowth() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getGrowth() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "GROWTH");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getHabitat() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getHabitat() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "HABITAT");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getLegislation() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getLegislation() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "LEGISLATION");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getLifeCycle() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getLifeCycle() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "LIFE_CYCLE");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getLifeExpectancy() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getLifeExpectancy() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "LIFE_EXPECTANCY");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getLookAlikes() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getLookAlikes() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "LOOK_ALIKES");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getManagement() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getManagement() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "MANAGEMENT");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getManagementProcedures() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getManagementProcedures() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "MANAGEMENT_PROCEDURES");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getMigration() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getMigration() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "MIGRATION");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getMolecularBiology() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getMolecularBiology() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "MOLECULAR_BIOLOGY");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getMorphology() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getMorphology() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "MORPHOLOGY");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getPopulationBiology() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getPopulationBiology() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "POPULATION_BIOLOGY");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getReproduction() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getReproduction() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "REPRODUCTION");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getRiskStatement() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getRiskStatement() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "RISK_STATEMENT");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getTaxonBiology() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getTaxonBiology() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "TAXON_BIOLOGY");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getThreats() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getThreats() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "THREATS");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getTrends() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getTrends() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "TRENDS");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getTrophicStrategy() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getTrophicStrategy() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "TROPHIC_STRATEGY");
				
				pstmt.execute();
				
				
			}
			
			if(!"".equals( model.getInfoItem().getUses() ) ) {
				
				
				pstmt.setString(2, model.getInfoItem().getUses() );
				
				pstmt.setString(3, "text/plain");
				
				pstmt.setString(4, "USES");
				
				pstmt.execute();
				
				
			}
      			
      		
      		
            pstmt.close();
            
            conn.close();
            
            
            
         }
        catch (SQLException sqle) {
            
            sqle.printStackTrace();
            
            throw new DataAccessException();
          
            
        }
        
		finally {
	            if( conn != null ) {
		            try 
		            { 		            	
		         
		                conn.close(); 
		                
		            }
		            catch(SQLException e) {
		                
		            	System.out.println("Connection error in finally block");
		                e.printStackTrace();
		               
		            }
	            }
	            
		}
		
		return itemId;
		

	}
	
	public InfoItem getInfoItemByTaxonId(int taxonId) throws DataAccessException {
		
		PreparedStatement pstmt = null;
		
        ResultSet rset = null;
        
        Connection conn = null;
        
        List itemList = null;

        InfoItem item = null;
       
        
      
        try {
            
        	conn = this.getDatasource().getConnection();        
            
            String query = "select id, taxon, content, format, category " +
            				"from info_item " + 
            				"where " +
            				"taxon = '" + taxonId + "'";           				
            				
            System.out.println(query);
            
            pstmt = conn.prepareStatement(query);
            
            rset = pstmt.executeQuery();   
            
            itemList = makeBeanList(resultsetToList(rset));
            
            item = setInfoItem(itemList);
            
            rset.close();
            
            pstmt.close();
            
            
                                               
            
        } 
        catch(SQLException e) {
        	
            e.printStackTrace();
            
            throw new DataAccessException();
            
        }
        finally {
                        
            
            try 
            { 
                
                conn.close();
                
                System.out.println("Connection closed");
                
            }
            catch(Exception e) {
                
                e.printStackTrace();
               
            }
            
            
            
        }

		
		return item;
		
	}
	
	private InfoItem setInfoItem(List itemList) {
		
		InfoItem spm = new InfoItem();
		
		Iterator i = itemList.iterator();
		
		int categoriesFilledCounter = 0;
		
		while(i.hasNext()) {
			
			InfoItem item = (InfoItem) i.next();
			
			
			//System.out.println(item.getId() + ", " + item.getCategory());
			
			if("ASSOCIATION".equals(item.getCategory().trim())) {
				
				spm.setAssociations(item.getContent());
				
				categoriesFilledCounter++;
				
			}
			
			if("DISEASES".equals(item.getCategory())) {
				
				spm.setDiseases(item.getContent());
				
				categoriesFilledCounter++;
				
			}
			
			if("DISTRIBUTION".equals(item.getCategory())) {
				
				spm.setDistribution(item.getContent());
				
				categoriesFilledCounter++;
				
			}
			
			if("HABITAT".equals(item.getCategory())) {
				
				spm.setHabitat(item.getContent());
				
				categoriesFilledCounter++;
				
			}
			
			if("LIFE_CYCLE".equals(item.getCategory())) {
				
				spm.setLifeCycle(item.getContent());
				
				categoriesFilledCounter++;
				
			}
			
			if("LIFE_EXPECTANCY".equals(item.getCategory())) {
				
				spm.setLifeExpectancy(item.getContent());
				
				categoriesFilledCounter++;
				
			}
			
			if("LOOK_ALIKES".equals(item.getCategory())) {
				
				spm.setLookAlikes(item.getContent());
				
				categoriesFilledCounter++;
				
			}
		}
		
		if(categoriesFilledCounter > 0 ) {
			
			return spm;
		}
		else {
			
			return null;
		}
			
	
		
	}
	
	/**
     * Preleva i dati del result set contenuti in un oggetto List e li salva in un 
     * bean appropriato, facendo corrispondere un bean a ciascun record.
     * Restituisce una lista di bean
     */
    protected List makeBeanList(List rows)
    {
        List beanList = new ArrayList();
         
        ListIterator rowIterator = rows.listIterator();
        
        int rowcounter = 0;
        
        while(rowIterator.hasNext()) {
        	
            rowcounter++;
            List fieldlist = (List)rowIterator.next();
            ListIterator fieldsIterator = fieldlist.listIterator();
            Object obj = null;
            String type = "";
            String columnName = "";
            InfoItem bean = new InfoItem();
            
            
            while(fieldsIterator.hasNext())
            {
                TableFieldHandler tfhandler = (TableFieldHandler)fieldsIterator.next();
                obj = tfhandler.getObject();
                type = tfhandler.getType();
                columnName = tfhandler.getColumnName().trim();
                // System.out.println(columnName);
                if( columnName.equalsIgnoreCase("ID") )
                {
                    bean.setId( ((Integer)obj).intValue() );
                    
                }                
                if( columnName.equalsIgnoreCase("TAXON") )
                {
                    bean.setTaxonId( ((Integer)obj).intValue() );
                    
                }
                if( columnName.equalsIgnoreCase("CONTENT") )
                {
                    bean.setContent( (String)obj );
                    
                }
                if( columnName.equalsIgnoreCase("CATEGORY") )
                {
                    bean.setCategory( (String)obj );
                    
                }
                if( columnName.equalsIgnoreCase("FORMAT") )
                {
                    bean.setFormat( (String)obj );
                    
                }                                               
                         
            }
            
            beanList.add(bean);
            
         }
        
        return beanList;
        
    }

}
